package birs.controller.expanditure.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.expanditure.XPD004Model;
import birs.model.expanditure.XPD004ModelA;
import birs.model.expanditure.XPD004ModelB;
import birs.model.expanditure.XPD004ModelC;
import birs.model.expanditure.XPD004ModelD;
import birs.model.expanditure.XPD004ModelE;
import birs.model.expanditure.XPD004ModelF;
import birs.util.ConnectionDB;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class XPD004Task {
	public void getExpenditureQuarterTree(HttpServletRequest request, HttpServletResponse response) throws IOException{
		double currentBudget = 0.0;
		double paySum = 0.0;
		
		//get Expenditure per year
		List<XPD004Model> xpd004ModelList = getExpenditureByYear();
		
		String periodYear = "(";
		for (XPD004Model xpd004Model : xpd004ModelList) {
			periodYear += xpd004Model.getName()+",";
		}
		periodYear = periodYear.substring(0,periodYear.lastIndexOf(",")) +")";
		
		//get Expenditure per Budget group
		List<XPD004ModelA> xpd004ModelAList = getExpenditureByBudgetGroup(periodYear);
		
		for (XPD004Model xpd004Model : xpd004ModelList) {
			List<XPD004ModelA> xpd004ModelAListTmp = new ArrayList<XPD004ModelA>();
			for (XPD004ModelA xpd004ModelA : xpd004ModelAList) {
				if(xpd004Model.getName() == xpd004ModelA.getBudgetPeriodId()){
					xpd004ModelAListTmp.add(xpd004ModelA);
				}
			}
			if(xpd004ModelAListTmp.size()>0){
				xpd004Model.setChildren(xpd004ModelAListTmp);
			}
			currentBudget += xpd004Model.getCurrentBudget();
			paySum += xpd004Model.getPaySum();
		}
		
		//get Expenditure per Department
		List<XPD004ModelB> xpd004ModelBList = getExpenditureByDepartment(periodYear);
		
		String departmentId = "";
		for (XPD004ModelA xpd004ModelA : xpd004ModelAList) {
			List<XPD004ModelB> xpd004ModelBListTmp = new ArrayList<XPD004ModelB>();
			for (XPD004ModelB xpd004ModelB : xpd004ModelBList) {
				if(xpd004ModelA.getBudgetPeriodId() == xpd004ModelB.getBudgetPeriodId() && xpd004ModelA.getBudgetGroupId() == xpd004ModelB.getBudgetGroupId()){
					xpd004ModelBListTmp.add(xpd004ModelB);
//					departmentId += xpd004ModelB.getDepartmentId()+",";
					departmentId += " OR D.DEPARTMENTID = "+xpd004ModelB.getDepartmentId();
				}
			}
			if(xpd004ModelBListTmp.size()>0){
				xpd004ModelA.setChildren(xpd004ModelBListTmp);
			}
		}
		//departmentId = departmentId.substring(0,departmentId.lastIndexOf(",")) + ")";
		
		//get Expenditure by project
		String projectId = "";
		List<XPD004ModelC> xpd004ModelCList = getExpenditureByProject(periodYear,departmentId);
		for (XPD004ModelB xpd004ModelB : xpd004ModelBList) {
			List<XPD004ModelC> xpd004ModelCListTmp = new ArrayList<XPD004ModelC>();
			for (XPD004ModelC xpd004ModelC : xpd004ModelCList) {
				if(xpd004ModelB.getBudgetPeriodId() == xpd004ModelC.getBudgetPeriodId() 
						&& xpd004ModelB.getBudgetGroupId() == xpd004ModelC.getBudgetGroupId() 
						&& xpd004ModelB.getDepartmentId() == xpd004ModelC.getDepartmentId()){
					xpd004ModelCListTmp.add(xpd004ModelC);
					projectId += " OR D.PROJECTID = "+xpd004ModelC.getProjectId();
				}
			}
			if(xpd004ModelCListTmp.size()>0){
				xpd004ModelB.setChildren(xpd004ModelCListTmp);
			}
		}
		//projectId = projectId.substring(0,projectId.lastIndexOf(","))+")";
		
		List<XPD004ModelD> xpd004ModelDList = getExpenditureByActivity(periodYear, departmentId, projectId);
		for (XPD004ModelC xpd004ModelC : xpd004ModelCList) {
			List<XPD004ModelD> xpd004ModelDListTmp = new ArrayList<XPD004ModelD>();
			for (XPD004ModelD xpd004ModelD : xpd004ModelDList) {
				if(xpd004ModelC.getBudgetPeriodId() == xpd004ModelD.getBudgetPeriodId() 
						&& xpd004ModelC.getBudgetGroupId() == xpd004ModelD.getBudgetGroupId() 
						&& xpd004ModelC.getDepartmentId() == xpd004ModelD.getDepartmentId() 
						&& xpd004ModelC.getProjectId() == xpd004ModelD.getProjectId()){
					xpd004ModelDListTmp.add(xpd004ModelD);
				}
			}
			xpd004ModelC.setChildren(xpd004ModelDListTmp);
		}
		
		XPD004ModelE xpd004ModelE = new XPD004ModelE();
		xpd004ModelE.setName("งบประมาณ");
		xpd004ModelE.setCurrentBudget(currentBudget);
		xpd004ModelE.setPaySum(paySum);
		xpd004ModelE.setChildren(xpd004ModelList);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd004ModelE);
		sendResponse(request, response, resultJson);
	}
	
	public void getDrilldownByPeriodYear(HttpServletRequest request, HttpServletResponse response){
		List<XPD004ModelF> xpd004ModelFList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelF> xpd004ModelF = new ResultSetMapper<XPD004ModelF>();
			xpd004ModelFList = xpd004ModelF.mapRersultSetToObject(getDrilldownByPeriodYearSQL(con, request.getParameter("budgetPeriodId")), XPD004ModelF.class);
			
			ResultSet rs = getCurrentBudgetByPeriodYearSQL(con, request.getParameter("budgetPeriodId"));
			double currentBudget = 0.0;
			while (rs.next()) {
				currentBudget = rs.getDouble(1);
			}
			
			String resultJson;
			if(xpd004ModelFList != null){
				List<XPD004ModelF> xpd004ModelFListTmp = new ArrayList<XPD004ModelF>();
				for (XPD004ModelF xpd004ModelF2 : xpd004ModelFList) {
					XPD004ModelF xpd004ModelFTmp = new XPD004ModelF();
					xpd004ModelFTmp.setBudgetPeriodId(Integer.parseInt(request.getParameter("budgetPeriodId")));
					xpd004ModelFTmp.setQuarter(xpd004ModelF2.getQuarter());
					xpd004ModelFTmp.setName("currentBudget");
					currentBudget -= xpd004ModelF2.getAmount();
					xpd004ModelFTmp.setAmount(currentBudget);
					xpd004ModelFListTmp.add(xpd004ModelFTmp);
				}
				xpd004ModelFList.addAll(xpd004ModelFListTmp);
				resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd004ModelFList);
			}else{
				resultJson = "No Data";
			}
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getDrilldownByBudgetGroup(HttpServletRequest request, HttpServletResponse response){
		List<XPD004ModelF> xpd004ModelFList = null;
		String select;
		String condition;
		if("99".equals(request.getParameter("budgetGroupId"))){
			select = "99 AS BUDGETGROUPID ,'เงินอื่นๆ' AS BUDGETGROUPNAME,";
			condition = "NOT IN (2,290)";
		}else{
			select = "D.BUDGETGROUPID , D.BUDGETGROUPNAME,";
			condition = " = "+request.getParameter("budgetGroupId");
		}
		
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelF> xpd004ModelF = new ResultSetMapper<XPD004ModelF>();
			xpd004ModelFList = xpd004ModelF.mapRersultSetToObject(getDrilldownByBudgetGroupSQL(con, request.getParameter("budgetPeriodId"), select, condition), XPD004ModelF.class);
			ResultSet rs = getCurrentBudgetSQL(con, request.getParameter("budgetPeriodId"), condition);
			
			double currentBudget = 0.0;
			while(rs.next()){
				currentBudget = rs.getDouble(1);
			}
			
			String resultJson;
			if(xpd004ModelFList != null){
				List<XPD004ModelF> xpd004ModelFListTmp = new ArrayList<XPD004ModelF>();
				for (XPD004ModelF xpd004ModelF2 : xpd004ModelFList) {
					XPD004ModelF xpd004ModelFTmp = new XPD004ModelF();
					//xpd004ModelFTmp.setBudgetPeriodId(xpd004ModelF2.getBudgetPeriodId());
					//xpd004ModelFTmp.setBudgetGroupId(xpd004ModelF2.getBudgetGroupId());
					//xpd004ModelFTmp.setBudgetGroupName(xpd004ModelF2.getBudgetGroupName());
					xpd004ModelFTmp.setQuarter(xpd004ModelF2.getQuarter());
					xpd004ModelFTmp.setName("currentBudget");
					currentBudget -= xpd004ModelF2.getAmount();
					xpd004ModelFTmp.setAmount(currentBudget);
					xpd004ModelFListTmp.add(xpd004ModelFTmp);
				}
				xpd004ModelFList.addAll(xpd004ModelFListTmp);
				resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd004ModelFList);
			}else{
				resultJson = "No Data";
			}
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getDrilldownByDepartment(HttpServletRequest request, HttpServletResponse response){
		List<XPD004ModelF> xpd004ModelFList = null;
		String select;
		String condition;
		if("99".equals(request.getParameter("budgetGroupId"))){
			select = "99 AS BUDGETGROUPID ,'เงินอื่นๆ' AS BUDGETGROUPNAME, D.DEPARTMENTID,D.DEPARTMENTNAME,";
			condition = "NOT IN (2,290) AND D.DEPARTMENTID = "+request.getParameter("departmentId");
		}else{
			select = "D.BUDGETGROUPID , D.BUDGETGROUPNAME,D.DEPARTMENTID,D.DEPARTMENTNAME,";
			condition = " = "+request.getParameter("budgetGroupId")+ " AND D.DEPARTMENTID = "+request.getParameter("departmentId");
		}
		
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelF> xpd004ModelF = new ResultSetMapper<XPD004ModelF>();
			xpd004ModelFList = xpd004ModelF.mapRersultSetToObject(getDrilldownByDepartmentSQL(con, request.getParameter("budgetPeriodId"), select, condition), XPD004ModelF.class);
			
			ResultSet rs = getCurrentBudgetSQL(con, request.getParameter("budgetPeriodId"), condition);
			double currentBudget = 0.0;
			while(rs.next()){
				currentBudget = rs.getDouble(1);
			}
			
			String resultJson;
			if(xpd004ModelFList != null){
				List<XPD004ModelF> xpd004ModelFListTmp = new ArrayList<XPD004ModelF>();
				for (XPD004ModelF xpd004ModelF2 : xpd004ModelFList) {
					XPD004ModelF xpd004ModelFTmp = new XPD004ModelF();
					//xpd004ModelFTmp.setBudgetPeriodId(xpd004ModelF2.getBudgetPeriodId());
					//xpd004ModelFTmp.setBudgetGroupId(xpd004ModelF2.getBudgetGroupId());
					//xpd004ModelFTmp.setBudgetGroupName(xpd004ModelF2.getBudgetGroupName());
					xpd004ModelFTmp.setQuarter(xpd004ModelF2.getQuarter());
					xpd004ModelFTmp.setName("currentBudget");
					currentBudget -= xpd004ModelF2.getAmount();
					xpd004ModelFTmp.setAmount(currentBudget);
					xpd004ModelFListTmp.add(xpd004ModelFTmp);
				}
				xpd004ModelFList.addAll(xpd004ModelFListTmp);
				resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd004ModelFList);
			}else{
				resultJson = "No data";
			}
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void getDrilldownByProject(HttpServletRequest request, HttpServletResponse response){
		List<XPD004ModelF> xpd004ModelFList = null;
		String select;
		String condition;
		if("99".equals(request.getParameter("budgetGroupId"))){
			select = "99 AS BUDGETGROUPID ,'เงินอื่นๆ' AS BUDGETGROUPNAME, D.DEPARTMENTID,D.DEPARTMENTNAME,D.PROJECTID,D.PROJECTNAME,";
			condition = "NOT IN (2,290) AND D.DEPARTMENTID = "+request.getParameter("departmentId")+"AND D.PROJECTID = "+request.getParameter("projectId");
		}else{
			select = "D.BUDGETGROUPID , D.BUDGETGROUPNAME,D.DEPARTMENTID,D.DEPARTMENTNAME,D.PROJECTID,D.PROJECTNAME,";
			condition = " = "+request.getParameter("budgetGroupId")+ " AND D.DEPARTMENTID = "+request.getParameter("departmentId")+"AND D.PROJECTID = "+request.getParameter("projectId");
		}
		
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelF> xpd004ModelF = new ResultSetMapper<XPD004ModelF>();
			xpd004ModelFList = xpd004ModelF.mapRersultSetToObject(getDrilldownByProjectSQL(con, request.getParameter("budgetPeriodId"), select, condition), XPD004ModelF.class);
			
			ResultSet rs = getCurrentBudgetSQL(con, request.getParameter("budgetPeriodId"), condition);
			double currentBudget = 0.0;
			while(rs.next()){
				currentBudget = rs.getDouble(1);
			}
			
			String resultJson;
			if(xpd004ModelFList != null){
				List<XPD004ModelF> xpd004ModelFListTmp = new ArrayList<XPD004ModelF>();
				for (XPD004ModelF xpd004ModelF2 : xpd004ModelFList) {
					XPD004ModelF xpd004ModelFTmp = new XPD004ModelF();
					//xpd004ModelFTmp.setBudgetPeriodId(xpd004ModelF2.getBudgetPeriodId());
					//xpd004ModelFTmp.setBudgetGroupId(xpd004ModelF2.getBudgetGroupId());
					//xpd004ModelFTmp.setBudgetGroupName(xpd004ModelF2.getBudgetGroupName());
					xpd004ModelFTmp.setQuarter(xpd004ModelF2.getQuarter());
					xpd004ModelFTmp.setName("currentBudget");
					currentBudget -= xpd004ModelF2.getAmount();
					xpd004ModelFTmp.setAmount(currentBudget);
					xpd004ModelFListTmp.add(xpd004ModelFTmp);
				}
				xpd004ModelFList.addAll(xpd004ModelFListTmp);
				resultJson = ConvertDataType.getInstance().objectToJasonArray(xpd004ModelFList);
			}else{
				resultJson = "No Data";
			}
			sendResponse(request, response, resultJson);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private List<XPD004Model> getExpenditureByYear(){
		List<XPD004Model> xpd004ModelList = null;
		try(Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004Model>  xpd004Model = new ResultSetMapper<XPD004Model>();
			xpd004ModelList = xpd004Model.mapRersultSetToObject(getExpenditureByYearSQL(con), XPD004Model.class);
		} catch (Exception e){
			e.printStackTrace();
		}
		return xpd004ModelList;
	}
	
	private List<XPD004ModelA> getExpenditureByBudgetGroup(String periodYear){
		List<XPD004ModelA> xpd004ModelAList = null;
		try(Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelA>  xpd004ModelA = new ResultSetMapper<XPD004ModelA>();
			xpd004ModelAList = xpd004ModelA.mapRersultSetToObject(getExpenditureByBudgetGroupSQL(con,periodYear), XPD004ModelA.class);
		} catch (Exception e){
			e.printStackTrace();
		}
		return xpd004ModelAList;
	}
	
	private List<XPD004ModelB> getExpenditureByDepartment(String periodYear){
		List<XPD004ModelB> xpd004BList = null;
		try(Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelB> xpd004ModelB = new ResultSetMapper<XPD004ModelB>();
			xpd004BList = xpd004ModelB.mapRersultSetToObject(getExpenditureByDepartmentSQL(con, periodYear), XPD004ModelB.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd004BList;
	}
	
	private List<XPD004ModelC> getExpenditureByProject(String periodYear,String departmentId){
		List<XPD004ModelC> xpd004ModelCList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelC> xpd004ModelC = new ResultSetMapper<XPD004ModelC>();
			xpd004ModelCList = xpd004ModelC.mapRersultSetToObject(getExpenditureByProjectSQL(con, periodYear,departmentId), XPD004ModelC.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd004ModelCList;
	}
	
	private List<XPD004ModelD> getExpenditureByActivity(String periodYear, String departmentId, String projectId){
		List<XPD004ModelD> xpd004ModelDList = null;
		try (Connection con = ConnectionDB.getMisConnection()){
			ResultSetMapper<XPD004ModelD> xpd004ModelD = new ResultSetMapper<XPD004ModelD>();
			xpd004ModelDList = xpd004ModelD.mapRersultSetToObject(getExpenditureByActivitySQL(con, periodYear, departmentId, projectId), XPD004ModelD.class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return xpd004ModelDList;
	}
	
	
	private ResultSet getExpenditureByYearSQL(Connection con){
		String sql = " 	SELECT D.BUDGETPERIODID,D.BUDGETPERIODID AS NAME, "
				+" 	  SUM(D.CURRENTBUDGET) AS CURRENTBUDGET,	"
				+" 	  SUM(D.PAYSUM) AS PAYSUM	"
				+" 	  FROM PROMIS.v_bud_linktype D	"
				+" 	  WHERE D.BUDGETPERIODID <=	(SELECT TO_CHAR(SYSDATE, 'yyyy', 'NLS_CALENDAR=''THAI BUDDHA'' NLS_DATE_LANGUAGE=THAI') AS YEAR	FROM DUAL) "
				+" 	  GROUP BY D.BUDGETPERIODID	"
				+" 	  ORDER BY D.BUDGETPERIODID ";

		return getData(sql, con);
	}
	
	private ResultSet getExpenditureByBudgetGroupSQL(Connection con, String periodYear){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+" 	  T.BUDGETGROUPNAME,	"
				+" 	  T.BUDGETGROUPNAME AS NAME,	"
				+" 	  SUM(T.CURRENTBUDGET) AS CURRENTBUDGET,	"
				+" 	  SUM(T.PAYSUM) AS PAYSUM	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID,	"
				+" 	    D.BUDGETGROUPID,	"
				+" 	    B.BUDGETGROUPNAME,	"
				+" 	    D.CURRENTBUDGET,	"
				+" 	    D.PAYSUM	"
				+" 	  FROM PROMIS.v_bud_linktype D ,	"
				+" 	    BUDGETGROUP B	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID   IN (2,290)	"
				+" 	  AND D.BUDGETGROUPID    = B.BUDGETGROUPID	"
				+" 	  	"
				+" 	  UNION ALL	"
				+" 	  	"
				+" 	  SELECT D.BUDGETPERIODID,	"
				+" 	    99      AS BUDGETGROUPID,	"
				+" 	    'เงินอื่น' AS BUDGETGROUPNAME,	"
				+" 	    D.CURRENTBUDGET,	"
				+" 	    D.PAYSUM	"
				+" 	  FROM PROMIS.v_bud_linktype D ,	"
				+" 	    BUDGETGROUP B	"
				+" 	  WHERE D.BUDGETPERIODID   IN "+periodYear
				+" 	  AND D.BUDGETGROUPID NOT IN (2,290)	"
				+" 	  AND D.BUDGETGROUPID      = B.BUDGETGROUPID	"
				+" 	  ) T	"
				+" 	GROUP BY T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+" 	  T.BUDGETGROUPNAME	"
				+" 	ORDER BY BUDGETPERIODID DESC,CURRENTBUDGET DESC ";

		return getData(sql,con);
	}
	
	private ResultSet getExpenditureByDepartmentSQL(Connection con, String periodYear){
		String sql = " SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID ,	"
				+"	  T.BUDGETGROUPNAME, "
				+" 	  T.DEPARTMENTID, 	"
				+" 	  T.DEPARTMENTNAME, 	"
				+" 	  T.DEPARTMENTNAME AS NAME, 	"
				+" 	  SUM(T.CURRENTBUDGET) AS CURRENTBUDGET, 	"
				+" 	  SUM(T.PAYSUM) AS PAYSUM	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID,D.BUDGETGROUPID,D.BUDGETGROUPNAME,D.DEPARTMENTID, D.DEPARTMENTNAME, D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID IN (2,290)	"
				+" 		"
				+" 	  UNION ALL	"
				+" 		"
				+" 	  SELECT  D.BUDGETPERIODID,99 AS BUDGETGROUPID,D.BUDGETGROUPNAME,D.DEPARTMENTID, D.DEPARTMENTNAME,D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID NOT IN (2,290)	"
				+" 	  ) T	"
				+" 	GROUP BY  T.BUDGETPERIODID,T.BUDGETGROUPID ,T.BUDGETGROUPNAME,T.DEPARTMENTID, T.DEPARTMENTNAME "
				+" 	ORDER BY BUDGETPERIODID DESC,CURRENTBUDGET DESC ";
		return getData(sql, con);
	}
	
	private ResultSet getExpenditureByProjectSQL(Connection con,String periodYear ,String departmentId){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+"	  T.BUDGETGROUPNAME, "
				+" 	  T.DEPARTMENTID,	"
				+" 	  T.DEPARTMENTNAME, 	"
				+" 	  T.PROJECTID,	"
				+" 	  T.PROJECTNAME,	"
				+" 	  T.PROJECTNAME AS NAME,	"
				+" 	  SUM(T.CURRENTBUDGET) AS CURRENTBUDGET,	"
				+" 	  SUM(T.PAYSUM) AS PAYSUM	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID,D.BUDGETGROUPID,D.BUDGETGROUPNAME,D.DEPARTMENTID, D.DEPARTMENTNAME, D.PROJECTID, D.PROJECTNAME, D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID IN (2,290)	"
				+	  departmentId
				+" 	  	"
				+" 	  UNION ALL	"
				+" 		"
				+" 	  SELECT  D.BUDGETPERIODID,99 AS BUDGETGROUPID,D.BUDGETGROUPNAME,D.DEPARTMENTID, D.DEPARTMENTNAME, D.PROJECTID, D.PROJECTNAME,D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID NOT IN (2,290)	"
				+	  departmentId
				+" 	  ) T	"
				+" 	GROUP BY  T.BUDGETPERIODID,T.BUDGETGROUPID ,T.BUDGETGROUPNAME,T.DEPARTMENTID,T.DEPARTMENTNAME,T.PROJECTID, T.PROJECTNAME	"
				+" 	ORDER BY BUDGETPERIODID DESC,CURRENTBUDGET DESC ";
		return getData(sql, con);
	}
	
	private ResultSet getExpenditureByActivitySQL(Connection con,String periodYear,String departmentId,String projectId){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID ,	"
				+" 	  T.DEPARTMENTID, 	"
				+" 	  T.PROJECTID, 	"
				+" 	  T.ACTIVITYID, 	"
				+" 	  T.ACTIVITYNAME,	"
				+" 	  SUM(T.CURRENTBUDGET) AS CURRENTBUDGET, 	"
				+" 	  SUM(T.PAYSUM) AS PAYSUM	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID,D.BUDGETGROUPID,D.DEPARTMENTID, D.DEPARTMENTNAME, D.PROJECTID, D.PROJECTNAME, D.ACTIVITYID, D.ACTIVITYNAME,D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID IN (2,290) "
				+	  departmentId 
				+	  projectId
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	  SELECT  D.BUDGETPERIODID,99 AS BUDGETGROUPID,D.DEPARTMENTID, D.DEPARTMENTNAME, D.PROJECTID, D.PROJECTNAME, D.ACTIVITYID, D.ACTIVITYNAME,D.CURRENTBUDGET, D.PAYSUM	"
				+" 	  FROM V_BUD_LINKTYPE D	"
				+" 	  WHERE D.BUDGETPERIODID IN "+periodYear
				+" 	  AND D.BUDGETGROUPID NOT IN (2,290) "
				+	  departmentId 
				+	  projectId
				+" 	  ) T	"
				+" 	GROUP BY  T.BUDGETPERIODID,T.BUDGETGROUPID ,T.DEPARTMENTID, T.PROJECTID,T.ACTIVITYID, T.ACTIVITYNAME	"
				+" 	ORDER BY BUDGETPERIODID DESC,CURRENTBUDGET DESC ";
		return getData(sql, con);
	}
	
	private ResultSet getDrilldownByPeriodYearSQL(Connection con,String periodYear){
		String sql = " 	SELECT T.BUDGETPERIODID, T.QUARTER ,'debit' AS NAME,SUM(T.DR) AS AMOUNT	"
				+" 	FROM	"
				+" 	(SELECT D.BUDGETPERIODID ,'quarter1' AS QUARTER,PI.DR	"
				+" 	FROM V_BUD_LINKTYPE D , PAYMENT P ,PAYMENTITEM PI	"
				+" 	WHERE 	"
				+" 	D.BUDGETID = PI.BUDGETID	"
				+" 	AND P.PAYMENTID = PI.PAYMENTID	"
				+" 	AND P.PAYMENTSTATUS = 'S'	"
				+" 	AND D.BUDGETPERIODID = "+periodYear
				+" 	AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/10/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/12/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT D.BUDGETPERIODID ,'quarter2' AS QUARTER, PI.DR	"
				+" 	FROM V_BUD_LINKTYPE D , PAYMENT P ,PAYMENTITEM PI	"
				+" 	WHERE 	"
				+" 	D.BUDGETID = PI.BUDGETID	"
				+" 	AND P.PAYMENTID = PI.PAYMENTID	"
				+" 	AND P.PAYMENTSTATUS = 'S'	"
				+" 	AND D.BUDGETPERIODID = "+periodYear
				+" 	AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/01/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/03/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT D.BUDGETPERIODID ,'quarter3' AS QUARTER ,PI.DR	"
				+" 	FROM V_BUD_LINKTYPE D , PAYMENT P ,PAYMENTITEM PI	"
				+" 	WHERE 	"
				+" 	D.BUDGETID = PI.BUDGETID	"
				+" 	AND P.PAYMENTID = PI.PAYMENTID	"
				+" 	AND P.PAYMENTSTATUS = 'S'	"
				+" 	AND D.BUDGETPERIODID = "+periodYear
				+" 	AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/04/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/06/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 		"
				+" 	UNION ALL	"
				+" 		"
				+" 	SELECT D.BUDGETPERIODID ,'quarter4' AS QUARTER,PI.DR	"
				+" 	FROM V_BUD_LINKTYPE D , PAYMENT P ,PAYMENTITEM PI	"
				+" 	WHERE 	"
				+" 	D.BUDGETID = PI.BUDGETID	"
				+" 	AND P.PAYMENTID = PI.PAYMENTID	"
				+" 	AND P.PAYMENTSTATUS = 'S'	"
				+" 	AND D.BUDGETPERIODID = "+periodYear
				+" 	AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/07/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/09/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	)T	"
				+" 	GROUP BY T.BUDGETPERIODID,T.QUARTER	"
				+" 	ORDER BY T.BUDGETPERIODID,T.QUARTER ";
		return getData(sql, con);
	}
	
	private ResultSet getDrilldownByBudgetGroupSQL(Connection con,String periodYear ,String select, String condition){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+" 	  T.BUDGETGROUPNAME,	"
				+" 	  T.QUARTER ,	"
				+" 	  'debit'   AS NAME,	"
				+" 	  SUM(T.DR) AS AMOUNT	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	   'quarter1' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/10/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/12/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	    'quarter2' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/01/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/03/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter3' AS QUARTER ,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/04/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/06/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter4' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/07/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/09/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  )T	"
				+" 	GROUP BY T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,T.BUDGETGROUPNAME,T.QUARTER	"
				+" 	ORDER BY T.BUDGETPERIODID,	"
				+" 	  T.QUARTER ";
		return getData(sql,con);
	}
	
	private ResultSet getDrilldownByDepartmentSQL(Connection con,String periodYear,String select,String condition){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+" 	  T.BUDGETGROUPNAME,	"
				+"	  T.DEPARTMENTID, "
				+" 	  T.DEPARTMENTNAME, "
				+" 	  T.QUARTER ,	"
				+" 	  'debit'   AS NAME,	"
				+" 	  SUM(T.DR) AS AMOUNT	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	   'quarter1' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/10/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/12/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	    'quarter2' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/01/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/03/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter3' AS QUARTER ,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/04/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/06/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter4' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/07/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/09/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  )T	"
				+" 	GROUP BY T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,T.BUDGETGROUPNAME,T.DEPARTMENTID,T.DEPARTMENTNAME,T.QUARTER"
				+" 	ORDER BY T.BUDGETPERIODID,	"
				+" 	  T.QUARTER ";
		return getData(sql, con);
	}
	
	private ResultSet getDrilldownByProjectSQL(Connection con,String periodYear,String select,String condition){
		String sql = " 	SELECT T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,	"
				+" 	  T.BUDGETGROUPNAME,	"
				+"	  T.DEPARTMENTID, "
				+" 	  T.DEPARTMENTNAME, "
				+"	  T.PROJECTID,"
				+"	  T.PROJECTNAME,"	
				+" 	  T.QUARTER ,	"
				+" 	  'debit'   AS NAME,	"
				+" 	  SUM(T.DR) AS AMOUNT	"
				+" 	FROM	"
				+" 	  (SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	   'quarter1' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/10/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/12/"+(Integer.parseInt(periodYear)-1)+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+      select
				+" 	    'quarter2' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/01/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('31/03/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter3' AS QUARTER ,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/04/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/06/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  UNION ALL	"
				+" 	  SELECT D.BUDGETPERIODID ,	"
				+       select
				+" 	    'quarter4' AS QUARTER,	"
				+" 	    PI.DR	"
				+" 	  FROM V_BUD_LINKTYPE D ,	"
				+" 	    PAYMENT P ,	"
				+" 	    PAYMENTITEM PI	"
				+" 	  WHERE D.BUDGETID     = PI.BUDGETID	"
				+" 	  AND P.PAYMENTID      = PI.PAYMENTID	"
				+" 	  AND P.PAYMENTSTATUS  = 'S'	"
				+" 	  AND D.BUDGETPERIODID = "+periodYear
				+" 	  AND D.BUDGETGROUPID "+condition
				+" 	  AND P.RECEIVEDATETIME BETWEEN TO_DATE('01/07/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha') AND TO_DATE('30/09/"+periodYear+"','DD/MM/YYYY','NLS_CALENDAR=''Thai Buddha')	"
				+" 	  )T	"
				+" 	GROUP BY T.BUDGETPERIODID,	"
				+" 	  T.BUDGETGROUPID,T.BUDGETGROUPNAME,T.DEPARTMENTID,T.DEPARTMENTNAME,T.PROJECTID,T.PROJECTNAME,T.QUARTER"
				+" 	ORDER BY T.BUDGETPERIODID,	"
				+" 	  T.QUARTER ";
		return getData(sql, con);
	}
	
	private ResultSet getCurrentBudgetByPeriodYearSQL(Connection con,String periodYear){
		String sql = " 	SELECT SUM(CURRENTBUDGET)	"
				+" 	FROM V_BUD_LINKTYPE	"
				+" 	WHERE BUDGETPERIODID ="+periodYear;

		return getData(sql, con);
	}
	
	private ResultSet getCurrentBudgetSQL(Connection con,String periodYear,String condition){
		String sql = " 	SELECT SUM(D.CURRENTBUDGET)	"
				+" 	FROM V_BUD_LINKTYPE	D"
				+" 	WHERE D.BUDGETPERIODID ="+periodYear
				+"	AND D.BUDGETGROUPID "+condition;
		return getData(sql, con);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getData(String sql,Connection con){
		ResultSet result = null;
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}
